﻿using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
using NorthwindDataservice.Models;
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace NorthwindDataservice
{
    class NorthwindDbContext : DbContext
    {
        public DbSet<Category> Categories { get; set; }
        public DbSet<Product> Products { get; set; }
        public DbSet<OrderDetails> OrderDetails { get; set; }
        public DbSet<Order> Orders { get; set; }

        /**
         * Logger factory injected with a new ConsoleLoggerProvider
         * that logs the command name at an informational level.
         */
        public static readonly LoggerFactory ConsoleLoggerFactory
        = new LoggerFactory(new[]
        {
            new ConsoleLoggerProvider((category, level)
                => category == DbLoggerCategory.Database.Command.Name
                    && level == LogLevel.Information, true)
        });

        /**
         * Overriding OnConfiguration providing our database credentials
         * from a JSON configuration file and enabling logging. 
         */
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            var config = GetConfig();

            // Getting psql connection string from configuration.
            var postgresCredentials = config.GetSection("psql").Value;

            // Using Postgresql with the previously read credentials.
            optionsBuilder.UseNpgsql(postgresCredentials);

            // Enable logging to see what happens behind the scenes.
            optionsBuilder.UseLoggerFactory(ConsoleLoggerFactory)
                .EnableSensitiveDataLogging();
        }

        /**
         * Overriding OnModelCreating to provide the mapping between the model and EFC.
         */
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // mapping for categories
            modelBuilder.Entity<Category>().ToTable("categories");
            //modelBuilder.Entity<Category>().HasMany(c => c.Products).WithOne(p => p.Category).HasForeignKey(p => p.CategoryId);
            modelBuilder.Entity<Category>().Property(c => c.Id).HasColumnName("categoryid").ValueGeneratedOnAdd();
            modelBuilder.Entity<Category>().Property(c => c.Name).HasColumnName("categoryname");
            modelBuilder.Entity<Category>().Property(c => c.Description).HasColumnName("description");

            // mapping for products
            modelBuilder.Entity<Product>().ToTable("products");
            //modelBuilder.Entity<Product>().HasMany(p => p.OrderDetails).WithOne(od => od.Product).HasForeignKey(od => od.ProductId);
            modelBuilder.Entity<Product>().Property(p => p.Id).HasColumnName("productid");
            modelBuilder.Entity<Product>().Property(p => p.Name).HasColumnName("productname");
            modelBuilder.Entity<Product>().Property(p => p.UnitPrice).HasColumnName("unitprice");
            modelBuilder.Entity<Product>().Property(p => p.QuantityPerUnit).HasColumnName("quantityperunit");
            modelBuilder.Entity<Product>().Property(p => p.UnitsInStock).HasColumnName("unitsinstock");
            modelBuilder.Entity<Product>().Property(p => p.CategoryId).HasColumnName("categoryid");

            // mapping for orderdetails
            modelBuilder.Entity<OrderDetails>().ToTable("orderdetails");
            //modelBuilder.Entity<OrderDetails>().HasOne(od => od.Order).WithMany(o => o.OrderDetails).HasForeignKey(od => od.OrderId);
            //modelBuilder.Entity<OrderDetails>().HasOne(od => od.Product).WithMany(p => p.OrderDetails).HasForeignKey(od => od.ProductId);
            modelBuilder.Entity<OrderDetails>().Property(od => od.UnitPrice).HasColumnName("unitprice");
            modelBuilder.Entity<OrderDetails>().Property(od => od.Quantity).HasColumnName("quantity");
            modelBuilder.Entity<OrderDetails>().Property(od => od.Discount).HasColumnName("discount");
            modelBuilder.Entity<OrderDetails>().Property(od => od.OrderId).HasColumnName("orderid");
            modelBuilder.Entity<OrderDetails>().Property(od => od.ProductId).HasColumnName("productid");
            modelBuilder.Entity<OrderDetails>().HasKey(od => new { od.OrderId, od.ProductId });

            // mapping for orders
            modelBuilder.Entity<Order>().ToTable("orders");
            //modelBuilder.Entity<Order>().HasMany(o => o.OrderDetails).WithOne(od => od.Order).HasForeignKey(od => od.OrderId);
            modelBuilder.Entity<Order>().Property(o => o.Id).HasColumnName("orderid");
            modelBuilder.Entity<Order>().Property(o => o.Date).HasColumnName("orderdate");
            modelBuilder.Entity<Order>().Property(o => o.Required).HasColumnName("requireddate");
            modelBuilder.Entity<Order>().Property(o => o.Shipped).HasColumnName("shippeddate");
            modelBuilder.Entity<Order>().Property(o => o.Freight).HasColumnName("freight");
            modelBuilder.Entity<Order>().Property(o => o.ShipName).HasColumnName("shipname");
            modelBuilder.Entity<Order>().Property(o => o.ShipCity).HasColumnName("shipcity");


        }

        private IConfigurationRoot GetConfig()
        {
            // Read the configurations from a JSON file.
            // @see https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/index?view=aspnetcore-2.1#json-configuration-provider
            string projectDirectory = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName;

            // if on windows (the working directory is different on linux and on windows)
            if (!projectDirectory.EndsWith("NorthwindDataservice"))
            {
                projectDirectory = Directory.GetParent(Directory.GetCurrentDirectory()).FullName + Path.DirectorySeparatorChar + "NorthwindDataservice";
            }

            return new ConfigurationBuilder()
                .SetBasePath(projectDirectory)
                .AddJsonFile("appsettings.development.json", optional: false, reloadOnChange: true)
                .Build();
        }
    }
}
